-- Function:

DROP SCHEMA IF EXISTS auditoria CASCADE;

CREATE SCHEMA auditoria;

ALTER TABLE AMBCFG DROP COLUMN IF EXISTS BITACORA;

ALTER TABLE AMBCFG ADD COLUMN BITACORA VARCHAR(1);

COMMENT ON COLUMN public.ambcfg.bitacora IS 'Tipo de bitacora que se llevara puede ser: N (No se lleva), T (Total), S (Selectiva), U (Todos los Updates), I (Todos los Insert)';


CREATE TABLE auditoria.bitacora (
   codambusracc varchar,
   clave     character varying, 
   esquema   character varying NOT NULL, 
   tabla     character varying NOT NULL, 
   operacion character varying(3) NOT NULL,
   campo     character varying    NOT NULL,
   valor_old character varying, 
   valor_new character varying, 
   fecha timestamp without time zone NOT NULL
);

ALTER TABLE auditoria.bitacora OWNER TO abacouser;


COMMENT ON TABLE  auditoria.bitacora              IS 'Lleva registro de todos los cambios realizados en la base. Esta tabla se carga desde la funcion de trigger tg_control_audit_instancia';
COMMENT ON COLUMN auditoria.bitacora.codambusracc IS 'usuario logueado en abaco que hizo la modificacion, se obtiene de la tabla auditoria.sesion buscando por el numero de sesion actual';
COMMENT ON COLUMN auditoria.bitacora.esquema      IS 'el esquema de la tabla modificada';
COMMENT ON COLUMN auditoria.bitacora.tabla        IS 'la tabla modificada';
COMMENT ON COLUMN auditoria.bitacora.operacion    IS 'Operacion: INSERT o UPDATE (no genera auditoria del delete por ahora)';
COMMENT ON COLUMN auditoria.bitacora.campo        IS 'Campo modificado';
COMMENT ON COLUMN auditoria.bitacora.valor_old    IS 'El valor del campo antes de ser modificado';
COMMENT ON COLUMN auditoria.bitacora.valor_new    IS 'El valor del campo despues de ser modificado';



CREATE TABLE auditoria.tablas (
   esquema   character varying NOT NULL, 
   tabla     character varying NOT NULL,
   signo     character varying(1) NOT NULL DEFAULT '+',
   operaciones character varying,
   CONSTRAINT tablas_pkey PRIMARY KEY (esquema, tabla, signo)
);

ALTER TABLE auditoria.tablas OWNER TO abacouser;

COMMENT ON TABLE  auditoria.tablas              IS 'Tablas a incluir en la bitacora (si ambcfg.bitacora = S)';
COMMENT ON COLUMN auditoria.tablas.esquema      IS 'el esquema de la tabla';
COMMENT ON COLUMN auditoria.tablas.tabla        IS 'la tabla';
COMMENT ON COLUMN auditoria.tablas.signo        IS '+ incluye (normal) - excluye.';
COMMENT ON COLUMN auditoria.tablas.operaciones  IS 'Puede ser: INSERT y/o UPDATE (separados por coma)';



CREATE TABLE auditoria.sesion (
   idSesion     integer NOT NULL, 
   codambusracc character varying,
   codambemp    character varying,
   codambsuc    character varying,
   codambequ    character varying,
   CONSTRAINT sesion_pkey PRIMARY KEY (idSesion)
);

ALTER TABLE auditoria.sesion OWNER TO abacouser;

COMMENT ON TABLE  auditoria.sesion              IS 'Tabla que vincula el id de sesion con el usuario actualmente logueado';
COMMENT ON COLUMN auditoria.sesion.idSesion     IS 'el id de la sesion corriente';
COMMENT ON COLUMN auditoria.sesion.codambusracc IS 'el usuario logueado';
COMMENT ON COLUMN auditoria.sesion.codambemp    IS 'la empresa logueada';
COMMENT ON COLUMN auditoria.sesion.codambsuc    IS 'el sucursal logueada';
COMMENT ON COLUMN auditoria.sesion.codambequ    IS 'el equipo logueado';


CREATE OR REPLACE FUNCTION auditoria.tabla_auditada(pEsquema character varying, pTabla character varying, pOperacion character varying)
  RETURNS boolean AS
$BODY$
DECLARE
  vBitacora varchar := bitacora from ambcfg;
BEGIN
  -- Funcion que indica si corresponde auditar tabla y operacion enviada por parametro
  CASE vBitacora 
  WHEN 'T' THEN 
     RETURN NOT EXISTS (SELECT * FROM auditoria.tablas WHERE tabla = pTabla AND esquema = pEsquema AND position(pOperacion in Operaciones) > 0 AND Signo = '-');
  WHEN 'S' THEN 
     RETURN EXISTS (SELECT * FROM auditoria.tablas WHERE tabla = pTabla AND esquema = pEsquema AND position(pOperacion in Operaciones) > 0 AND Signo = '+');
  WHEN 'U' THEN  
     RETURN pOperacion = 'UPDATE' AND NOT EXISTS (SELECT * FROM auditoria.tablas WHERE tabla = pTabla AND esquema = pEsquema AND position(pOperacion in Operaciones) > 0 AND Signo = '-');
  WHEN 'I' THEN   
     RETURN pOperacion = 'INSERT' AND NOT EXISTS (SELECT * FROM auditoria.tablas WHERE tabla = pTabla AND esquema = pEsquema AND position(pOperacion in Operaciones) > 0 AND Signo = '-');
  WHEN 'D' THEN   
     RETURN pOperacion = 'DELETE' AND NOT EXISTS (SELECT * FROM auditoria.tablas WHERE tabla = pTabla AND esquema = pEsquema AND position(pOperacion in Operaciones) > 0 AND Signo = '-');
  ELSE RETURN false;
  END CASE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION auditoria.tabla_auditada(character varying, character varying, character varying)
  OWNER TO abacouser;


CREATE OR REPLACE FUNCTION public.aaloginwebparametros(json_sesion character varying)
  RETURNS character varying AS
$BODY$
DECLARE
  json_pedido  varchar;
  json_convertido varchar;
  cMov         refCursor;
  vMoneda varchar;
  vFecha  varchar;
  vIdSesion integer;
  vComillas varchar;
BEGIN

 -- Migracion colada en esta funcion
 /* Desactivada el 18/8/2021
  IF NOW() > '20210701' THEN
    UPDATE AFIP.cndivaempxcndivapad SET letra = 'A' WHERE codcndivaemp = 'I' AND codcndivapad = 'M' AND letracom = 'v' AND letra = 'B';
  END IF;
 */

  /* 
     ATENCION: Los json van entre llaves, pero para que ODBC no se queje al migrar los armamos con 
     chr(123) = llave abre
     chr(125) = llave cierra

     En este comentario, en la realidad vienen las llaves

     Funcion que se llama inmediatamente despues del login para determinar parametros para enviar a la pagina servicios_login.php
     que devolvera un json que se pasa a la funcion aaloginwebacciones para tomar accion de algo.
     recibe un json_sesion de la forma : 

        <llave abre>@codambusracc@:@...@,@codambequ@: ... ,@codambemp@:@...@,@codambsuc@:@...@<llave cierra>

        lo primero que se hace es cambiar las @ por comilla doble y queda un json bien formado.   

     el json_pedido puede tener cualquier cosa que debera ser interpretada por la pagina servicios_login.php
        
  */

  -- recomposicion del JSON como un verdadero JSON.

  vComillas := chr(34);
  json_sesion := replace(json_sesion, '@',vComillas);

  --
  -- Se genera en la tabla auditoria.sesion un registro para vincular la sesion actual con el usuario logueado en abacoGestion
  -- si hubo un cambio de usuario la sesion se mantiene, con lo cual se actualiza el registro.


  IF (select aaversion_postgres_necesaria(9,3)) AND EXISTS (SELECT * FROM AMBCFG WHERE BITACORA <> 'N') THEN
     vIdSesion    := (SELECT pg_backend_pid());
     IF EXISTS (SELECT * FROM auditoria.sesion WHERE idSesion = vIdSesion) THEN
        UPDATE auditoria.sesion SET 
           codambusracc = cast(cast(json_sesion as json) ->> 'codambusracc' as varchar), 
           codambemp    = cast(cast(json_sesion as json) ->> 'codambemp' as varchar), 
           codambsuc    = cast(cast(json_sesion as json) ->> 'codambsuc' as varchar), 
           codambequ    = cast(cast(json_sesion as json) ->> 'codambequ' as varchar) 
        WHERE idSesion = vIdSesion;
     ELSE   
        INSERT INTO auditoria.sesion values (vIdSesion, cast(cast(json_sesion as json) ->> 'codambusracc' as varchar),
					  	          cast(cast(json_sesion as json) ->> 'codambemp' as varchar), 
								  cast(cast(json_sesion as json) ->> 'codambsuc' as varchar), 
						    	  cast(cast(json_sesion as json) ->> 'codambequ' as varchar)
					 				); 
     END IF;
  END IF;
 -- Fin de registro de la sesion.

  IF NOT (select aaversion_postgres_necesaria(9,3))THEN
     RETURN '';
  ELSE
     IF strpos((select ini from public.ambcfg),'CotizacionManual') > 0 THEN 
        RETURN '';
     ELSE
       json_pedido := chr(123) || vComillas || 'ultimasCotizaciones' || vComillas || ':['; 

       OPEN cMov FOR SELECT VALMON.AFIPCOD, coalesce(TO_CHAR(MAX(VIGENCIA),'YYYYMMdd'),'20210101')
       FROM VALMON
       LEFT JOIN VALCTZ ON VALMON.COD = VALCTZ.CODVALMON
       WHERE AFIPCOD IS NOT NULL AND AFIPCOD <> 'PES' 
       GROUP BY VALMON.AFIPCOD;

       FETCH cMov INTO vMoneda, vFecha;

       LOOP
         IF NOT FOUND THEN EXIT; END IF;
         json_pedido := json_pedido || chr(123) || vComillas || 'moneda' || vComillas || ':' || vComillas || vMoneda || vComillas || ',' 
                                                || vComillas || 'fecha'  || vComillas || ':' || vComillas || vFecha  || vComillas || chr(125) || ',';
         FETCH cMov INTO vMoneda, vFecha;
       END LOOP;
       CLOSE cMov;

       json_pedido := substr(json_pedido,1, length(json_pedido) - 1) || ']' || chr(125);
       return json_pedido;  
     END IF;
 END IF;    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.aaloginwebparametros(character varying)
  OWNER TO abacouser;

-- Funcion de trigger modificada para que arme la bitacora si corresponde para la operacion que lo invoca.


CREATE OR REPLACE FUNCTION tg_control_audit_instancia()
  RETURNS trigger AS
$BODY$

DECLARE noEstaImportando boolean;
	esInstanciaLibre boolean;
        esControlada boolean;
        esConWrapper boolean;
        operacion varchar;
	instancia int4;

  /* AUDITORIA */

    xCodAmbUsrAcc varchar;
    xTabla   VARCHAR(100):= TG_TABLE_NAME;
    xEsquema VARCHAR(100):= TG_TABLE_SCHEMA;
    xOperacion VARCHAR(50) := TG_OP;
    Campo VARCHAR(100);
    Old_value VARCHAR;
    New_value VARCHAR;
    Fecha     TIMESTAMP WITHOUT TIME ZONE := NOW();
    Clave     VARCHAR(100) := '';
    valorClave VARCHAR(100);

    Field INT := 0;
    MaxField INT := MAX(ORDINAL_POSITION)
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = xTabla and TABLE_SCHEMA = xEsquema;
  /* AUDITORIA */
	
BEGIN
  /* AUDITORIA */
   IF auditoria.tabla_auditada(xEsquema, xTabla, xOperacion) AND 
      ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') OR (TG_OP = 'DELETE')) THEN
      -- Tengo que armar la clave de la tabla primero  
      WHILE Field < MaxField LOOP
        SELECT MIN(C.ORDINAL_POSITION) INTO Field
            FROM INFORMATION_SCHEMA.COLUMNS C
            INNER JOIN information_schema.key_column_usage PK ON PK.table_name = C.table_name and PK.table_schema = C.table_schema and C.column_name = PK.column_name 
            INNER JOIN pg_catalog.pg_constraint CT ON CT.conname = PK.constraint_name AND CT.conType = 'p'
            WHERE C.TABLE_NAME = xTabla AND C.TABLE_SCHEMA = xEsquema AND C.ORDINAL_POSITION > Field ;

        SELECT COLUMN_NAME INTO Campo
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = xTabla AND TABLE_SCHEMA = xEsquema AND ORDINAL_POSITION = Field;




        IF Campo is not null THEN   
           IF ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT')) THEN
               EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO valorClave USING NEW;
           ELSE
               EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO valorClave USING OLD;
           END IF;    
           Clave := Clave || valorClave || ',';
        END IF;
      END LOOP;

      Field :=0;

      xCodAmbUsrAcc := (SELECT codambusracc FROM auditoria.sesion WHERE idsesion = pg_backend_pid()); 

      -- Recorro los campos y grabo los modificados.

      WHILE Field < MaxField LOOP
        SELECT MIN(ORDINAL_POSITION) INTO Field
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = xTabla AND TABLE_SCHEMA = xEsquema AND ORDINAL_POSITION > Field;

        SELECT C.COLUMN_NAME INTO Campo
            FROM INFORMATION_SCHEMA.COLUMNS C
            WHERE TABLE_NAME = xTabla AND TABLE_SCHEMA = xEsquema AND ORDINAL_POSITION = Field;

        IF Campo <> 'descxlike' THEN 
           CASE xOperacion
              WHEN 'INSERT' THEN
                Old_value := NULL;
                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO New_value USING NEW;
              WHEN 'UPDATE' THEN   
                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO Old_value USING OLD;
                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO New_value USING NEW;
              WHEN 'DELETE' THEN   
                EXECUTE 'SELECT ($1).' || quote_ident(Campo) || '::text' INTO Old_value USING OLD;
                New_Value := Null;
           END CASE;
           
           IF Old_value IS DISTINCT FROM New_value THEN
              INSERT INTO
                auditoria.bitacora (
                    codambusracc,
                    clave,
	            esquema,
                    tabla,
                    campo,
                    operacion,
                    valor_old,
                    valor_new,
                    fecha
                ) VALUES (
                    xCodAmbUsrAcc,
                    trim(Clave,','),
                    xEsquema,
                    xTabla,
                    Campo,
                    CASE xOperacion WHEN 'UPDATE' THEN 'UPD'
                                    WHEN 'INSERT' THEN 'INS'
				    WHEN 'DELETE' THEN 'DEL'
                    END,
                    Old_value,
                    New_value,
                    Fecha
                );
          END IF;
       END IF;
    END LOOP;
    
    END IF; -- FUE UPDATE

  /* AUDITORIA */



  SELECT (IdAmbDB Is NULL) INTO noEstaImportando FROM sinc_offline.logimportacion;
  --Si el IdAmbDB es nulo se está trabajando con el sistema de gestión,
  --si tiene un valor se está importando, no hay que controlar.
  
  SELECT (InstanciaLibre = 'S') INTO esInstanciaLibre FROM ambCfg;
  --Instancia libre hace que no se controlen los cambios
  
  SELECT (COALESCE(logControl,'S') = 'S') INTO esControlada FROM ambTabVer WHERE (UPPER(Tabla) = UPPER(TG_RELNAME));
  --si está en AMBTABVER con logcontrol = S es una tabla controlada, si no lo encuentra y tiene un valor NULO tambien
  
  SELECT COALESCE(IdAmbDB,0) INTO instancia FROM AmbCFG;
  --guardo el IdAmbDB en una variable, teniendo en cuenta el valor nulo

  SELECT NOT (IdAmbDB Is NULL) INTO esConWrapper FROM ambcfg;
  --Si se utiliza el esquema de wrapper
 
  --Parte que controla el DELETE y UPDATE
  IF ((TG_OP = 'DELETE') OR (TG_OP = 'UPDATE')) THEN
      IF (esConWrapper) AND (esControlada) AND (noEstaImportando) AND (NOT esInstanciaLibre) AND 
         (COALESCE(OLD.IdAmbDB,0) <> instancia) THEN
        Operacion := 'modificar'; 
        IF TG_OP = 'DELETE' THEN
          Operacion := 'eliminar';
        END IF;
        RAISE EXCEPTION 'No se puede % este registro, pertenece a otra sucursal. Intentando actualizar en tabla: %', operacion, TG_RELNAME;
      END IF;
  END IF;

  --Auditoria para cuando hace un INSERT
  IF TG_OP = 'INSERT' THEN
     NEW.FechaInsert := localtimestamp;
  
     IF (esConWrapper) AND (noEstaImportando) AND (NEW.IdAmbDB IS NULL) THEN
       NEW.IdAmbDB := instancia;  
       UPDATE sinc_offline.logntrans SET momentotrans = NEW.FechaInsert;	
     END IF;
  END IF;

  --Auditoria para cuando hace un UPDATE
  IF TG_OP = 'UPDATE' THEN
    NEW.FechaUpdate := localtimestamp; 
  
    IF (esConWrapper) AND (noEstaImportando) AND (COALESCE(NEW.IdAmbDB,0) = instancia) THEN
       UPDATE sinc_offline.logntrans SET momentotrans = NEW.FechaUpdate;
    END IF;
  END IF;

  IF ((TG_OP = 'INSERT') OR (TG_OP = 'UPDATE')) THEN
      RETURN NEW;
  END IF;
  IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tg_control_audit_instancia()
  OWNER TO abacouser;




-- El update lo hago acá luego de actualizar la función del trigger si no se dispara.

UPDATE AMBCFG SET BITACORA = 'N';

-- Clave primara para prexart (para poder detectarla en la auditoría)


-- ==================================
   SELECT aaVersion('465.00','464.09');
-- ==================================




